package com.example.qxfw.ybzz.mapper;

import com.baomidou.dynamic.datasource.annotation.DS;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.qxfw.common.entity.qx.TownshipForecast;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.sql.Timestamp;
import java.util.List;

@Mapper
@DS("qx")
public interface TownshipForecastMapper extends BaseMapper<TownshipForecast> {

    @Select("select NTimes, windD, WindS, Rain, Cloud, maxTemp, minTemp, Humid from datechar=${forecastTime} and TimeChar=${strTimeChar} and stationid  = ${stationID}")
    List<TownshipForecast> selectForecast1(String forecastTime, String strTimeChar, String stationID);

    @Delete("delete from township_forecast where datechar=${dateChar} and TimeChar=${strTimeChar} and stationid in (${stationList})")
    Integer delete(String dateChar, String strTimeChar, String stationList);

    @Select("select  DateChar, timechar " +
            "from township_forecast  " +
            "order by datechar,timechar desc " +
            "limit 1 ")
    List<TownshipForecast> selectMaxTime();

    @Select("select longitude, latitude, stationid, stationname, areaname, weather1, maxtemp, mintemp, windd, winds " +
            "from township_forecast " +
            "where datechar='${dateChar}' and timechar='${timeChar}' and (ntimes<='${nTimes1}' and ntimes> '${nTimes2}') and city like '${city}%' ")
    List<TownshipForecast> selectForecast(Timestamp dateChar, String timeChar, Integer nTimes1, Integer nTimes2, String city);

    @Select("select (case when maxtemp=-65535 then 0 else maxtemp end %2b case when mintemp=-65535 then 0 else mintemp end) / 2 as maxtemp " +
            "from township_forecast " +
            "where datechar = '${dateChar}'AND NTimes = '24'AND TimeChar = '20'AND stationID = '${stationId}' and (case when maxtemp=-65535 then 0 else maxtemp end %2b case when mintemp=-65535 then 0 else mintemp end) / 2 < #{maxTemp} ")
    List<TownshipForecast> selectList1(String dateChar, String stationId, String maxTemp);

    @Select("select (case when maxtemp=-65535 then 0 else maxtemp end %2b case when mintemp=-65535 then 0 else mintemp end) / 2 as maxtemp " +
            "from township_forecast " +
            "where datechar = '${dateChar}'AND NTimes = '24'AND TimeChar = '20'AND stationID = '${stationId}' and (case when maxtemp=-65535 then 0 else maxtemp end %2b case when mintemp=-65535 then 0 else mintemp end) / 2 between #{minTemp} and #{maxTemp} ")
    List<TownshipForecast> selectList2(String dateChar, String stationId, String minTemp, String maxTemp);

    @Select("select avg(case when humid=-65535 then 0 else humid end) as humid " +
            "from township_forecast " +
            "where datechar = '${dateChar}'AND NTimes = '24'AND TimeChar = '20'AND stationID = '${stationId}' " +
            "having avg(case when humid=-65535 then 0 else humid end) as humid > #{minTemp}")
    List<TownshipForecast> selectList3(String dateChar, String stationId, String minTemp);

    @Select("select avg(case when humid=-65535 then 0 else humid end) as humid " +
            "from township_forecast " +
            "where datechar = '${dateChar}'AND NTimes = '24'AND TimeChar = '20'AND stationID = '${stationId}' " +
            "having avg(case when humid=-65535 then 0 else humid end) as humid > #{maxTemp}")
    List<TownshipForecast> selectList4(String dateChar, String stationId, String maxTemp);

    @Select("select avg(case when humid=-65535 then 0 else humid end) as humid " +
            "from township_forecast " +
            "where datechar = '${dateChar}'AND NTimes = '24'AND TimeChar = '20'AND stationID = '${stationId}' " +
            "having avg(case when humid=-65535 then 0 else humid end) as humid between #{minTemp} and #{maxTemp} ")
    List<TownshipForecast> selectList5(String dateChar, String stationId, String minTemp, String maxTemp);

    @Select("select stationid, stationname, Weather1, WindS,  WindD, MaxTemp, MinTemp, NTimes " +
            "from township_forecast " +
            "where datechar='${date}' and timechar='${time}' and (NTimes>='${nTimes1}'and NTimes<='${nTimes2}') and stationid like '5%'  " +
            "order by stationid desc")
    List<TownshipForecast> selectList6(Timestamp date, String time, String nTimes1, String nTimes2);

    @Select("select stationname, Weather1, WindS,  WindD, MaxTemp, MinTemp, NTimes " +
            "from township_forecast " +
            "where datechar='${date}' and timechar='${time}' and (NTimes>='${nTimes1}'and NTimes<='${nTimes2}') and stationid = '${stationId}' ")
    List<TownshipForecast> selectList7(Timestamp date, String time, String nTimes1, String nTimes2, String stationId);

    @Select("select StationID, StationName, Longitude, Latitude, Weather1, MaxTemp, MinTemp, WindS, WindD, AreaName " +
            "from township_forecast " +
            "where datechar='${date}' and timechar='${time}' and (NTimes>='${nTimes1}'and NTimes<='${nTimes2}') " +
            "order by stationid desc,NTimes asc")
    List<TownshipForecast> selectList8(Timestamp date, String time, String nTimes1, String nTimes2);

    @Select("select cast(datechar as timestamp)+ CAST(timechar||' hour' as INTERVAL) as datechar " +
            "from township_forecast " +
            "order by datechar desc")
    List<String> selectDateChar();

    @Select("select rain " +
            "from township_forecast " +
            "where datechar='${dateChar}' and timechar='${timeChar}' and NTimes='${nTimes}' and Rain ${rain} and stationid='${stationId}'")
    List<String> selectRain(String dateChar, String timeChar, String nTimes, String rain, String stationId);

    @Select("select (MaxTemp + MinTemp)/2 as MaxTemp " +
            "from township_forecast " +
            "where datechar='${dateChar}' and timechar='${timeChar}' and NTimes='${nTimes}' and (MaxTemp+MinTemp)/2 ${temp} and stationid='${stationId}'")
    List<String> selectTemp(String dateChar, String timeChar, String nTimes, String temp, String stationId);

    @Select("select WindS " +
            "from township_forecast " +
            "where datechar='${dateChar}' and timechar='${timeChar}' and NTimes='${nTimes}' and WindS ${winds} and stationid='${stationId}'")
    List<String> selectWindS(String dateChar, String timeChar, String nTimes, String winds, String stationId);


    @Select("select press " +
            "from township_forecast " +
            "where datechar='${dateChar}' and timechar='${timeChar}' and NTimes='${nTimes}' and press ${press} and stationid='${stationId}'")
    List<String> selectPress(String dateChar, String timeChar, String nTimes, String press, String stationId);

    @Select("select Humid " +
            "from township_forecast " +
            "where datechar='${dateChar}' and timechar='${timeChar}' and NTimes='${nTimes}' and humid ${humid} and stationid='${stationId}'")
    List<String> selectHumid(String dateChar, String timeChar, String nTimes, String humid, String stationId);

    @Select("select (case when maxtemp=-65535 then 0 else maxtemp end + case when mintemp=-65535 then 0 else mintemp end) / 2 as maxtemp " +
            "from township_forecast " +
            "where datechar = '${dateChar}'AND NTimes = '${nTimes}'AND TimeChar = '${timeChar}'AND stationID = '${stationId}' ")
    List<Double> selectMaxTemp(String dateChar, String nTimes, String timeChar, String stationId);

    @Select("select avg(case when humid=-65535 then 0 else humid end) as humid " +
            "from township_forecast " +
            "where datechar = '${dateChar}'AND NTimes = '${nTimes}'AND TimeChar = '${timeChar}'AND stationID = '${stationId}' " +
            "AND humid between '${minHumid}' and '${maxHumid}'")
    List<Double> selectAvgHumid(String dateChar, String nTimes, String timeChar, String stationId, Double minHumid, Double maxHumid);

    @Select("SELECT stationid, stationname, longitude, latitude, " +
            "       min(case when humid is null then 0 else humid end) as humid, " +
            "       max(case when maxtemp is null then 0 else maxtemp end) as maxtemp, " +
            "       max(case when winds is null then 0 else winds end) as winds, " +
            "       sum(case when rain is null then 0 else rain end) as rain " +
            "FROM township_forecast " +
            "WHERE datechar = #{timestamp} AND timechar = #{timeChar} AND ntimes <= 24 " +
            "GROUP BY stationid, stationname, longitude, latitude ")
    List<TownshipForecast> selectByDatecharAndTimeChar(String timeChar, Timestamp timestamp);

    @Select("SELECT stationid, stationname, CAST(case when rain =-9999 then 0 else rain end  AS NUMERIC(10, 1)) as rain " +
            "FROM township_forecast " +
            "WHERE datechar='${dateChar}' AND timechar='${timeChar}' AND ntimes='${nTimes}' AND city like '${city}%' " +
            "GROUP BY stationid, stationname ")
    List<TownshipForecast> selectByDatecharAndTimeCharANDNtimesAndCtiy(Timestamp dateChar, String timeChar, int nTimes, String city);
}
